Imports System.Data.OleDb
Imports System.Data.OracleClient

Public Class myDBConn

    Const ConnStr As String = "DATA SOURCE=BSPT2;USER ID=sysadm;PASSWORD=sysadm;"

    Public Function myOleDbTable(ByVal SQL As String) As DataTable
        Dim cn As OleDbConnection
        Dim dsTemp As DataSet
        Dim dsCmd As OleDbDataAdapter
        Dim ConnStrMdb As String


        ConnStrMdb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ccapps\persistent\ccapps.mdb"

        cn = New OleDbConnection(ConnStrMdb)

        dsCmd = New OleDbDataAdapter(SQL, cn)
        dsTemp = New DataSet
        dsCmd.Fill(dsTemp, "Query")
        Return dsTemp.Tables(0)
        dsTemp.Dispose()
    End Function

    Public Shared Function myORATable(ByVal SQL As String) As DataTable
        Dim cn As OracleConnection
        Dim dsTemp As DataSet
        Dim dsCmd As OracleDataAdapter
        cn = New OracleConnection(ConnStr)
        Try
            dsCmd = New OracleDataAdapter(SQL, cn)
            dsTemp = New DataSet
            dsCmd.Fill(dsTemp, "Query")
            cn.Close()

            Return dsTemp.Tables(0)
            dsTemp.Dispose()
        Catch ex As Exception
            cn.Close()
        End Try
    End Function
    Public Function myOraResult(ByVal SQL As String) As OracleDataReader
        Dim cn As OracleConnection
        Dim dbcmd As OracleCommand
        Dim reader As OracleDataReader
        Dim dsTemp As DataSet
        Dim dsCmd As OracleDataAdapter
        Dim ConnStr As String
        Dim list As ArrayList

        ConnStr = "DATA SOURCE=RTPR_BCV;USER ID=sysadm;PASSWORD=sysadm;Max Pool Size=100;Min Pool Size=5;Pooling=yes"

        cn = New OracleConnection(ConnStr)
        cn.Open()

        dbcmd = cn.CreateCommand
        dbcmd.CommandText = SQL
        reader = dbcmd.ExecuteReader(CommandBehavior.CloseConnection)

        Return reader



    End Function



    Public Function Execute(ByVal SQL As String) As Long

        Dim ConnStr As String
        Dim lngResult As Long = 0
        Dim cn As New OleDbConnection(ConnStr)
        ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ccapps\persistent\ccapps.mdb"
        Try
            Dim objCmd As New OleDbCommand(SQL, cn)
            objCmd.Connection.Open()
            lngResult = objCmd.ExecuteNonQuery
            cn.Close()
        Catch ex As Exception
            cn.Close()
        End Try

        Return lngResult
    End Function

    Public Function ORAExecute(ByVal SQL As String) As Long

        'Dim ConnStr As String
        'ConnStr = "DATA SOURCE=BSPR_BCV;USER ID=sysadm;PASSWORD=sysadm;"
        Dim cn As New OracleConnection(ConnStr)
        Try
            Dim objCmd As New OracleCommand(SQL, cn)
            Dim lngResult As Long = 0

            objCmd.Connection.Open()
            lngResult = objCmd.ExecuteNonQuery
            cn.BeginTransaction.Commit()

            cn.Close()
            Return lngResult

        Catch ex As Exception
            cn.Close()
        End Try
    End Function


End Class



